﻿using System;
using System.Data;
using System.Data.SqlClient;
namespace MyFunction.MsSqlHelper
{
    /// <summary>
    /// Ms sql 数据库操作类
    /// </summary>
    public class MsSqlHelper
    {

        // Declartion internal variables
        /// <summary>
        /// 联接对象
        /// </summary>
        private SqlConnection m_connection = null;
        /// <summary>
        /// 联接字串
        /// </summary>
        private string m_connectionString = "";
        /// <summary>
        /// SqlDataAdapter
        /// </summary>
        private SqlDataAdapter m_dataAdapter = null;
        /// <summary>
        ///  查询结果集
        /// </summary>
        private DataSet m_dataSet = null;
        /// <summary>
        ///  主键
        /// </summary>
        private string m_fieldNameID = "";

        // The DataSet is filled with the methode LoadDataSet
        /// <summary>
        ///  属性：查询结果集
        /// </summary>
        public DataSet DataSet
        {
            get { return m_dataSet; }
        }

        // Constructor -> ConnectionString is required
        /// <summary>
        ///  构造函数，给联接字串赋值
        /// </summary>
        /// <param name="connectionString"></param>
        public MsSqlHelper ( string connectionString )
        {
            m_connectionString = connectionString;
        }

        // Load the DataSet 
        /// <summary>
        ///  数据查询，返回数据用DataSet 属性获取
        ///  有主键时，会生成m_dataAdapter  insert ,delete ,update  方法
        /// </summary>
        /// <param name="commandText">sql 语句</param>
        /// <param name="fieldNameID">主键</param>
        /// <returns>true ,false</returns>
        public bool Load ( string commandText , string fieldNameID )
        {
            // Save the variables
            m_fieldNameID = fieldNameID;

            try
            {
                // 打开联接
                m_connection = new SqlConnection ( m_connectionString );
                m_connection.Open ( );

                //建立 DataAdapter
                m_dataAdapter = new SqlDataAdapter ( commandText , m_connection );
                m_dataSet = new DataSet ( );

                //如果关键字不空，则生成插入、更新、删除指令
                if (!string.IsNullOrEmpty ( fieldNameID ))
                {
                    SqlCommandBuilder commandBuilder = new SqlCommandBuilder ( m_dataAdapter );
                    m_dataAdapter.InsertCommand = commandBuilder.GetInsertCommand ( );
                    m_dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand ( );
                    m_dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand ( );
                }

                // 填充dataSet
                m_dataAdapter.Fill ( m_dataSet );

                // 返回值
                return true;
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Altijd netjes sluiten
                m_connection.Close ( );
            }
        }


        // Load the DataSet ,多态
        /// <summary>
        ///  数据查询，返回数据用DataSet 属性获取
        /// </summary>
        /// <param name="commandText">sql 语句</param>
        /// <returns>true ,false </returns>
        public bool Load ( string commandText )
        {
            return Load ( commandText , "" );
        }
        //------------------------------------

#region 数据保存
        /// <summary>
        /// 数据保存，在Load 事件中要设定关键字段
        /// </summary>
        /// <returns>true ,false </returns>
        public bool Save ( )
        {
            // Hij kan alleen gegevens opslaan in ID bekend is
            //如查关键字为空，则直接返回
            if (m_fieldNameID.Trim ( ).Length == 0)
            {
                return false;
            }

            try
            {
                // Open de connectie
                m_connection.Open ( );

                //调用 dataadapter 更新方法，更新 dataset
                m_dataAdapter.Update ( m_dataSet );

                // We zijn hier, alles okay!
                return true;
            }

            catch (Exception)
            {
                throw;
            }
            finally
            {
                // Altijd netjes sluiten
                m_connection.Close ( );
            }
        }
#endregion
        #region
        /// <summary>
        ///  无返回值查询
        /// </summary>
        /// <param name="sqlstr">Sql 语句</param>
        /// <returns>true ,false </returns>
        public bool ExecuteNonQuery ( string sqlstr )
        {
            m_connection = new SqlConnection ( m_connectionString );
            m_connection.Open ( );
            SqlCommand cmd = new SqlCommand ( sqlstr , m_connection );
            try
            {
                cmd.ExecuteNonQuery ( );
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                m_connection.Close ( );
            }
        }
        #endregion

        #region
        /// <summary>
        ///  有结果集查询
        /// </summary>
        /// <param name="SqlCom">sql 语句</param>
        /// <returns>SqlDataReader 结果集</returns>
        public SqlDataReader ExceRead ( string SqlCom )
        {

            m_connection.Open ( );
            SqlCommand sqlcom = new SqlCommand ( SqlCom , m_connection );
            SqlDataReader read = sqlcom.ExecuteReader ( );
            return read;
        }
    }
        #endregion

}
